package cn.com.chec.en.dao;

import cn.com.chec.en.model.domain.BusinessInfo;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import org.apache.log4j.Logger;
import org.springframework.stereotype.Repository;

@Repository
public class BusinessInfoDao {
	protected static final Logger log = Logger.getLogger(BusinessInfoDao.class);

	public boolean insert(BusinessInfo info) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			conn.setAutoCommit(false);

			PreparedStatement pstmtHasUpload = conn
					.prepareStatement("select count(*) as count from tbl_business_i where bi_lgid_fk = ? and bi_chid_fk = ?");

			pstmtHasUpload.setInt(1, info.getLanguageId());
			pstmtHasUpload.setInt(2, info.getChannelId());
			ResultSet rs = pstmtHasUpload.executeQuery();
			int count = 0;
			if (rs.next()) {
				count = rs.getInt("count");
			}

			if (count > 0) {
				PreparedStatement pstmtUpdate = conn
						.prepareStatement("update tbl_business_i  set bi_content = ? where bi_lgid_fk = ? and bi_chid_fk = ?");

				int i = 0;
				i++;
				pstmtUpdate.setString(i, info.getBiContent());
				i++;
				pstmtUpdate.setInt(i, info.getLanguageId());
				i++;
				pstmtUpdate.setInt(i, info.getChannelId());
				pstmtUpdate.executeUpdate();
			} else {
				PreparedStatement pstmtInsert = conn
						.prepareStatement("insert into tbl_business_i(bi_lgid_fk,bi_chid_fk,bi_file,bi_content) values(?,?,?,?)");

				int i = 0;
				i++;
				pstmtInsert.setInt(i, info.getLanguageId());
				i++;
				pstmtInsert.setInt(i, info.getChannelId());
				i++;
				pstmtInsert.setString(i, info.getBiFile());
				i++;
				pstmtInsert.setString(i, info.getBiContent());
				pstmtInsert.execute();
			}
			conn.commit();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public List<BusinessInfo> getSubList(int languageId, int channelId,
			int start, int num) {
		List<BusinessInfo> list = new ArrayList<BusinessInfo>();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_business_i where 1=1 "
					+ (languageId > 0 ? " and bi_lgid_fk = ? " : "")
					+ (channelId > 0 ? " and bi_chid_fk = ? " : "")
					+ " limit ? ,?";
			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			if (languageId > 0) {
				i++;
				pstmt.setInt(i, languageId);
			}
			if (channelId > 0) {
				i++;
				pstmt.setInt(i, channelId);
			}
			i++;
			pstmt.setInt(i, start);
			i++;
			pstmt.setInt(i, num);

			ResultSet rs = pstmt.executeQuery();
			BusinessInfo info = null;
			while (rs.next()) {
				info = new BusinessInfo();
				info.setBiId(rs.getInt("bi_id"));
				info.setLanguageId(rs.getInt("bi_lgid_fk"));
				info.setChannelId(rs.getInt("bi_chid_fk"));
				info.setBiContent(rs.getString("bi_content"));

				list.add(info);
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	public int getCount(int languageId, int channelId) {
		int count = 0;
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select count(1) as count from tbl_business_i where 1=1 "
					+ (languageId > 0 ? " and bi_lgid_fk = ? " : "")
					+ (channelId > 0 ? " and bi_chid_fk = ? " : "");

			PreparedStatement pstmt = conn.prepareStatement(sql);

			int i = 0;
			if (languageId > 0) {
				i++;
				pstmt.setInt(i, languageId);
			}
			if (channelId > 0) {
				i++;
				pstmt.setInt(i, channelId);
			}
			ResultSet rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt("count");
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return count;
	}

	public BusinessInfo getBIByBID(int bid) {
		BusinessInfo info = new BusinessInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_business_i where bi_id = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, bid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				info.setBiId(rs.getInt("bi_id"));
				info.setLanguageId(rs.getInt("bi_lgid_fk"));
				info.setChannelId(rs.getInt("bi_chid_fk"));
				info.setBiContent(rs.getString("bi_content"));
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return info;
	}

	public BusinessInfo getBIByChIdLgId(int languageId, int channelId) {
		BusinessInfo info = new BusinessInfo();
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			String sql = "select * from tbl_business_i where bi_lgid_fk = ? and bi_chid_fk = ?";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, languageId);
			pstmt.setInt(2, channelId);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				info.setBiId(rs.getInt("bi_id"));
				info.setLanguageId(rs.getInt("bi_lgid_fk"));
				info.setChannelId(rs.getInt("bi_chid_fk"));
				info.setBiContent(rs.getString("bi_content"));
			}
			rs.close();
			pstmt.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return info;
	}

	public boolean deleteBusiness(int bid) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmt = conn
					.prepareStatement("delete from tbl_business_i where bi_id = ?");
			pstmt.setInt(1, bid);
			pstmt.execute();
			pstmt.close();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean udpateBusiness(BusinessInfo info) {
		try {
			Connection conn = DBFactory.getInstance().getConnection();
			PreparedStatement pstmtUpdate = conn
					.prepareStatement("update tbl_business_i  set bi_content = ?,bi_lgid_fk = ? ,bi_chid_fk= ? where  bi_id = ?");

			int i = 0;
			i++;
			pstmtUpdate.setString(i, info.getBiContent());
			i++;
			pstmtUpdate.setInt(i, info.getLanguageId());
			i++;
			pstmtUpdate.setInt(i, info.getChannelId());
			i++;
			pstmtUpdate.setInt(i, info.getBiId());
			pstmtUpdate.executeUpdate();
			conn.close();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return false;
	}
}